package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.HashMap;

/**
 * Connection to the meetings table
 * 
 * Performs DAO operations for the meetings table in the database
 * @author Sukhjinder K. Narula
 * @since June 2013
 * @version $Revision: 1.0 $
 */
public class MeetingSqlDao implements MeetingDao {
	
	 
	// Connection
	private Connection conn;
    
    /**
     * Constructor
     * @param conn connection object for database
     */
    public MeetingSqlDao(Connection conn) {
		this.conn = conn;
	}
    
    /**
     * Creates a new entry and returns the ID
     * @param properties 
    
     * @return int
     * @throws DaoException
     * @throws ModelException  * @see dao.MeetingDao#create(HashMap<String,String>)
     */
    public int create(HashMap<String, String> properties) throws DaoException{
    	
    	int toReturn = -1;
    	try {
    		String sql = "INSERT INTO meetings (UserId,Owner, RevisionId, Topic,C_Topic, Location,C_Location, Start,C_Start, End,C_End, Attendees,C_Attendees, Description,C_Description) VALUES (?,?,?,?,?,?,?,?,?, ?, ?, ?, ?, ?,?) ;";
	    	PreparedStatement statement = conn.prepareStatement(sql);
	    	statement.setInt(1, Integer.valueOf(properties.get("UserId")));
	    	statement.setString(2, properties.get("Owner"));
	    	statement.setString(3, properties.get("RevisionId"));
	    	statement.setString(4, properties.get("Topic"));
	    	statement.setString(5, properties.get("C_Topic"));
	    	statement.setString(6, properties.get("Location"));
	    	statement.setString(7, properties.get("C_Location"));
	    	statement.setTimestamp(8, Timestamp.valueOf(properties.get("Start")));
	    	statement.setTimestamp(9, Timestamp.valueOf(properties.get("C_Start")));
	    	statement.setTimestamp(10, Timestamp.valueOf(properties.get("End")));
	    	statement.setTimestamp(11, Timestamp.valueOf(properties.get("C_End")));
	    	statement.setString(12, properties.get("Attendees"));
	    	statement.setString(13, properties.get("C_Attendees"));
	    	statement.setString(14, properties.get("Description"));
	    	statement.setString(15, properties.get("C_Description"));
	    	statement.executeUpdate();
	    	Statement st = conn.createStatement();
        	ResultSet generatedKeys = st.executeQuery("SELECT seq FROM sqlite_sequence WHERE name = 'meetings' ;");
        	if (generatedKeys.next()) {
        	    toReturn = generatedKeys.getInt("seq");
        	}
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
    	return toReturn;
    }
    
    /**
     * Loads a meeting row given ID
    
    
    
     * @param Id int
     * @return properties * @throws DaoException
     * @throws ModelException  * @see dao.MeetingDao#load(int)
     */
    public HashMap<String, String> load(int Id) throws DaoException{
    	
    	HashMap<String, String> toReturn = null;
    	try {
	    	String sql = "SELECT * FROM meetings WHERE Id = ? LIMIT 1; ";
	    	PreparedStatement statement = conn.prepareStatement(sql);
	    	statement.setInt(1, Id);
	    	ResultSet rs = statement.executeQuery();
	    	toReturn = new HashMap<String, String>();
	    	while (rs.next()) {
	    		toReturn.put("Id", String.valueOf(Id));
	    		toReturn.put("UserId", rs.getString("UserId"));
	    		toReturn.put("Owner", rs.getString("Owner"));
	    		toReturn.put("RevisionId", rs.getString("RevisionId"));
	    		toReturn.put("Topic", rs.getString("Topic"));
	    		toReturn.put("C_Topic", rs.getString("C_Topic"));
	    		toReturn.put("Location", rs.getString("Location"));
	    		toReturn.put("C_Location", rs.getString("C_Location"));
	    		toReturn.put("Start", rs.getString("Start"));
	    		toReturn.put("C_Start", rs.getString("C_Start"));
	    		toReturn.put("End", rs.getString("End"));
	    		toReturn.put("C_End", rs.getString("C_End"));
	    		toReturn.put("Attendees", rs.getString("Attendees"));
	    		toReturn.put("C_Attendees", rs.getString("C_Attendees"));
	    		toReturn.put("Description", rs.getString("Description"));
	    		toReturn.put("C_Description", rs.getString("C_Description"));
	    	}
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
    	return toReturn;
    }

    /**
     * Updates a meeting
     * @param properties
    
     * @throws DaoException
     * @throws ModelException  * @see dao.MeetingDao#update(HashMap<String,String>)
     */
    public void update(HashMap<String, String> properties) throws DaoException {
    
    	
    	try {
    		String sql = "UPDATE meetings SET Topic = ?, Owner=?, RevisionId=?,C_Topic=?, Location = ?, C_Location=?, Start = ?, " +
    				"C_Start=?, End = ?,C_End=?, Attendees = ?,C_Attendees=?, Description = ? C_Description=? WHERE Id = ?; ";
	    	PreparedStatement statement = conn.prepareStatement(sql);
	    	statement.setString(1, properties.get("Topic"));
	    	statement.setString(2, properties.get("Owner"));
	    	statement.setString(3, properties.get("RevisionId"));
	    	statement.setString(4, properties.get("C_Topic"));
	    	statement.setString(5, properties.get("Location"));
	    	statement.setString(6, properties.get("C_Location"));
	    	statement.setTimestamp(7, Timestamp.valueOf(properties.get("Start")));
	    	statement.setTimestamp(8, Timestamp.valueOf(properties.get("C_Start")));
	    	statement.setTimestamp(9, Timestamp.valueOf(properties.get("End")));
	    	statement.setTimestamp(10, Timestamp.valueOf(properties.get("C_End")));
	    	statement.setString(11, properties.get("Attendees"));
	    	statement.setString(12, properties.get("C_Attendees"));
	    	statement.setString(13, properties.get("Description"));
	    	statement.setString(14, properties.get("C_Description"));
	    	statement.setInt(15, Integer.valueOf(properties.get("Id")));
	      	statement.executeUpdate();
	      	
    	} catch (SQLException e){
    		
    		throw new DaoException(e.getMessage());
    		
    	}
    }

	/**
	 * Deletes a user row, given the id
	
	
	 * @param Id int
	 * @throws DaoException
	 * @throws ModelException  * @see dao.MeetingDao#delete(int)
	 */
	public void delete(int Id) throws DaoException {
		
		try {
	    	String sql = "DELETE FROM meetings WHERE Id = ? ; ";
	    	PreparedStatement statement = conn.prepareStatement(sql);
	    	statement.setInt(1, Id);
	    	statement.executeUpdate();
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
	}
	
	/**
	 * Given the userId, returns all the meetings
	 * @param userId
	
	
	 * @return HashMap * @throws DaoException * @see dao.MeetingDao#findByUserId(int)
	 */
	public HashMap<Integer, String[]> findByUserId(int userId) throws DaoException {

		HashMap<Integer, String[]> properties = new HashMap<>();
		int index = 0, key;
        PreparedStatement statement;
		try {
			statement = conn.prepareStatement("SELECT * FROM meetings WHERE UserId = ? ORDER BY Topic ;");
			statement.setInt(1, userId);
	        ResultSet rs = statement.executeQuery();
        	while (rs.next()){
        		String meetingProperties[] = new String[13];
        		index = 0;
        		key = rs.getInt(1);
        		meetingProperties[index++] = rs.getString(2);
        		meetingProperties[index++] = rs.getString(3);
        		meetingProperties[index++] = rs.getString(4);
        		meetingProperties[index++] = rs.getString(5);
        		meetingProperties[index++] = rs.getString(6);
        		meetingProperties[index++] = rs.getString(7);
        		meetingProperties[index++] = rs.getString(8);
        		meetingProperties[index++] = rs.getString(9);
        		meetingProperties[index++] = rs.getString(10);
        		meetingProperties[index++] = rs.getString(11);
        		meetingProperties[index++] = rs.getString(12);
        		meetingProperties[index++] = rs.getString(13);
        		meetingProperties[index++] = rs.getString(14);
        		meetingProperties[index++] = rs.getString(15);
        		meetingProperties[index++] = rs.getString(16);
        		
        		properties.put(key, meetingProperties);
        	}                   
	        rs.close();  
		} catch (SQLException e) {
			throw new DaoException(e.getMessage());
		}                  
        return properties;
	}
	
	/**
	 * Given a user id, delete all the meetings
	 * @param userId
	
	 * @throws DaoException
	 * @throws ModelException  * @see dao.MeetingDao#deleteByUserId(int)
	 */
	public void deleteByUserId(int userId) throws DaoException {
		
		try {
	    	String sql = "DELETE FROM meetings WHERE UserId = ? ; ";
	    	PreparedStatement statement = conn.prepareStatement(sql);
	    	statement.setInt(1, userId);
	    	statement.executeUpdate();	
    	} catch (SQLException e){
    		throw new DaoException(e.getMessage());
    	}
		
	}
	
}